/* Scripts for building a closure table to handle the hierarchy relationships, 
	as opposed to searching for children concepts via the fullname+'%' wildcard string matching */

/* Add an integer primary key to the i2b2 metadata */
alter table dbo.i2b2 
add meta_id int identity (1,1) primary key;
go

-- Closure table design
-- compound primary key, with each member as FK reference to the new primary key meta_id in dbo.i2b2
-- path length stores the distance each descendent is from the referenced ancestor
-- for i2b2 purposes a concept and all its children will be any path_length >= 0 
-- Direct children can be found by querying for path_length = 1 for example.  
-- Path_length can be omitted for i2b2 if desired, but the integer impacts the table size very little.
-- It could also be used to lazy load the ontology if one was to rewrite segments of the ontology cell.  

create table MetaTree (
	ancestor int not null, 
	descendent int not null,
	path_length tinyint not null,
	PRIMARY KEY (ancestor, descendent), 
	FOREIGN KEY (ancestor) REFERENCES dbo.i2b2(meta_id),
	FOREIGN KEY (descendent) REFERENCES dbo.i2b2(meta_id)
);
go


-- SET UP SELF REFERENCE
INSERT INTO MetaTree (ancestor, descendent, path_length)
SELECT meta_id as ancestor, meta_id as descendent, 0 as path_length
from dbo.i2b2;
go

-- SET UP ANC/DESC RELATIONS
-- ATTENTION: For stock metadata or metadata built similar to the stock with c_dimcode being the 
-- long text field with a path-to-root string delimited by \ 
-- The below join that finds all ancestor/descendent relationships makes use of the c_dimcode field
-- to find all descendents that contain a given ancestors c_dimcode in their own dimcode field.  
-- It also calculates path_length

INSERT INTO MetaTree (ancestor, descendent, path_length)
select a.meta_id as ancestor, b.meta_id as descendent, b.c_hlevel - a.c_hlevel as pl
from dbo.i2b2 a left join dbo.i2b2 b
		on PATINDEX(a.c_dimcode+'%', b.c_dimcode) = 1
		and a.c_hlevel < b.c_hlevel
where b.meta_id is not null;
go




/* EXAMPLE USAGE


SELECT D.C_BASECODE FROM DBO.I2B2 D JOIN DBO.METATREE A 	ON A.descendant = D.META_ID 
	AND A.ANCESTOR=3827


In the above example, the meta_id for 'Diabetes Mellitus', the high level folder for ICD9 250.xx,
is 3827 in our system.  The above will pull all children regardless of their distance from 'Diabetes Mellitus'.

One could use this example as I have said earlier to display only the next level of children in the 
hierarchy for Diabetes by restricting it to a PATH_LENGTH of 1.

SELECT D.C_BASECODE FROM DBO.I2B2 D JOIN DBO.METATREE A 	ON A.descendant = D.META_ID 
	AND A.ANCESTOR=3827
where a.path_length = 1

The benefit from this approach is partially due to the nature of integer key relationships
between the metadata table and the closure table, but also due to the nature of the SQL Optimizer.
With the available statistics, the optimizer can "know" beforehand that it can look for a very specific
list of concepts from the metadata, instead of planning to search through all fullname fields 
to find children concepts with similar paths.  

*/



